




alter    procedure p_voufilordernoreset (
  @fyear int,
  @fmonth int,
  @binder tinyint,
  @filid integer,
  @ordertype tinyint,  
  @resetcount integer out
) as  
  declare @vouyearmonth varchar(6);
  declare @voucherid integer;
  declare @i integer;
  declare @local_tab table(voucherid integer)
begin
  set nocount on
  set @vouyearmonth=cast(@fyear as char(4))+ right(cast(100+@fmonth as char(3)),2)
  set @i = 1;
  set @resetcount = 0;
  if (@binder = 1)
  begin
    if @ordertype=0
      insert into @local_tab    
      select voucherid from tfb_voucher where vouyearmonth = @vouyearmonth and filid=@filid
        order by bdate, voucherid
    else if @ordertype=1
      insert into @local_tab    
      select voucherid from tfb_voucher where vouyearmonth = @vouyearmonth and filid=@filid
        order by filorderno
    declare cur_voufilordernoreset cursor for select * from @local_tab 
    open cur_voufilordernoreset 
    fetch next from cur_voufilordernoreset into @voucherid 
    while @@fetch_status = 0 
    begin
      update tfb_voucher set filorderno = @i where voucherid = @voucherid and filid=@filid
      set @i = @i + 1;
      set @resetcount = @resetcount + 1;
      fetch next from cur_voufilordernoreset into @voucherid 
    end
    close cur_voufilordernoreset
    deallocate cur_voufilordernoreset
  end
  else 
  begin
    select @i=max(filorderno) from tfb_voucher where vouyearmonth = @vouyearmonth and (binder_by is not null) and filid=@filid;
    set @i = isnull(@i,0) + 1;
    if @ordertype=0
      insert into @local_tab    
      select voucherid from tfb_voucher where vouyearmonth = @vouyearmonth and filid=@filid and binder_by is null
        order by bdate, voucherid
    else if @ordertype=1
      insert into @local_tab    
      select voucherid from tfb_voucher where vouyearmonth = @vouyearmonth and filid=@filid and binder_by is null
        order by filorderno
    declare cur_voufilordernoreset cursor for select * from @local_tab 
    open cur_voufilordernoreset 
    fetch next from cur_voufilordernoreset into @voucherid 
    while @@fetch_status = 0 
    begin
      update tfb_voucher set filorderno = @i where voucherid = @voucherid and filid=@filid
      set @i = @i + 1;
      set @resetcount = @resetcount + 1;
      fetch next from cur_voufilordernoreset into @voucherid 
    end
    close cur_voufilordernoreset
    deallocate cur_voufilordernoreset
  end
end






GO
